﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Gallio.Framework;
using MbUnit.Framework;
using System.Linq;

/* ***************************************************** *
 * Developed by Philip Japikse, www.japikse.blogspot.com *
 * see License.txt for license information               *
 * ***************************************************** */
namespace TSTIntegration.ADO
{
    public class SQLTestHarness
    {
        private readonly string _appSettingTargetConnectionName;
        private readonly string _appSettingTSTConnectionName;
        /// <summary>
        /// Constructor for T-SQL Test Tool Harness Class
        /// </summary>
        /// <param name="appSettingTSTConnectionName">Connection string for TST Database</param>
        /// <param name="appSettingTargetConnectionName">Connection string for System Under Test</param>
        public SQLTestHarness(string appSettingTSTConnectionName, string appSettingTargetConnectionName)
        {
            _appSettingTargetConnectionName = appSettingTargetConnectionName;
            _appSettingTSTConnectionName = appSettingTSTConnectionName;
        }
        /// <summary>
        /// Runs the TST Tests and returns the results as a list of TestResultsData
        /// </summary>
        /// <param name="suiteName">Optional TST Suite Name - blank runs all tests</param>
        public IList<TestResultData> RunSQLTestsReturningTestResultData(string suiteName)
        {
            int sessionID;
            ExecuteSQLAndReturnResult(suiteName, out sessionID);
            var results = ConvertTableToTestResults(GetResultData(sessionID));
            CleanTestResults(sessionID);
            return results;
        }
        public IEnumerable<Test> RunSQLTestsReturningTestCases(string suiteName, TestContext testContext)
        {
            var results = RunSQLTestsReturningTestResultData(suiteName);
            foreach (var suite in GetDistinctTestSuites(results))
            {
                var testSuite = new TestSuite(suite);
                var s = suite;
                foreach (var sp in GetDistinctSprocsPerSuite(results,s))
                {
                    var spName = sp;
                    testSuite.Children.Add(
                            new TestCase(
                                spName, () =>
                                                 {
                                                     foreach (var a in GetTestResultsForASproc(results, spName))
                                                     {
                                                         testContext.AddAssertCount(1);
                                                         Assert.IsTrue(a.AssertPassed, a.LogMessage);
                                                     }
                                                 }));
                }
                yield return testSuite;

            }
        }
        internal static IList<string> GetDistinctTestSuites(IList<TestResultData> results)
        {
            return results.Select(x => x.SuiteName).Distinct().ToList();
        }
        internal static IList<string> GetDistinctSprocsPerSuite(IList<TestResultData> results, string suiteName)
        {
            return results.Where(y => y.SuiteName == suiteName).Select(x => x.StoredProcName).Distinct().ToList();
        }
        internal static IList<TestResultData> GetTestResultsForASproc(IList<TestResultData> results, string sprocName)
        {
            return results.Where(x => x.StoredProcName == sprocName).Select(x => x).ToList();
        }
        internal bool ExecuteSQLAndReturnResult(string suiteName, out int sessionID)
        {
            using (var connection = new SqlConnection(ConfigurationManager.AppSettings[_appSettingTargetConnectionName]))
            {
                var command = new SqlCommand { Connection = connection, CommandType = CommandType.StoredProcedure };
                command.Parameters.Add(new SqlParameter("@TestDatabaseName", connection.Database));
                if (!String.IsNullOrEmpty(suiteName))
                {
                    command.CommandText = "TST.Runner.RunSuite";
                    command.Parameters.Add(new SqlParameter("@SuiteName", suiteName));
                }
                else
                {
                    command.CommandText = "TST.Runner.RunAll";
                }
                command.Parameters.Add(new SqlParameter("@Verbose", 1));
                command.Parameters.Add(new SqlParameter("@ResultsFormat", "Text"));
                command.Parameters.Add(new SqlParameter("@CleanTemporaryData", "0"));
                var sessionIDparam = new SqlParameter("@TestSessionId", SqlDbType.Int) { Direction = ParameterDirection.Output };
                command.Parameters.Add(sessionIDparam);
                var sessionPassedparam = new SqlParameter("@TestSessionPassed", SqlDbType.Bit) { Direction = ParameterDirection.Output };
                command.Parameters.Add(sessionPassedparam);
                try
                {
                    connection.Open();
                    command.ExecuteNonQuery();
                    sessionID = (int)command.Parameters["@TestSessionID"].Value;
                    return (bool)command.Parameters["@TestSessionPassed"].Value;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }
            }
        }
        internal void CleanTestResults(int sessionID)
        {
            using (var connection = new SqlConnection(ConfigurationManager.AppSettings[_appSettingTSTConnectionName]))
            {
                try
                {
                    using (
                        var cleanCommand = new SqlCommand("TST.Internal.CleanSessionData", connection) { CommandType = CommandType.StoredProcedure })
                    {
                        cleanCommand.Parameters.Add(new SqlParameter("@TestSessionId", sessionID));
                        connection.Open();
                        cleanCommand.ExecuteNonQuery();
                    }
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }
            }
        }
        internal DataTable GetResultData(int sessionID)
        {
            var dtResults = new DataTable("Results");
            using (var connection = new SqlConnection(ConfigurationManager.AppSettings[_appSettingTSTConnectionName]))
            {
                try
                {
                    connection.Open();
                    using (var command = new SqlCommand())
                    {
                        command.CommandType = CommandType.Text;
                        command.Connection = connection;
                        command.CommandText = "SELECT LogEntryId, TestSessionId, SuiteId, SuiteName, SuiteStatus, "
                                              + "TestId, SProcName, TestStatus, EntryType, LogMessage, CreatedTime "
                                              + "FROM Data.TSTResultsEx WHERE TestSessionID = " + sessionID;
                        using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            if (reader != null)
                            {
                                dtResults.Load(reader);
                            }
                        }
                    }
                }
                finally
                {
                    if (connection.State != ConnectionState.Closed)
                    {
                        connection.Close();
                    }
                }
            }
            return dtResults;
        }
        internal static IList<TestResultData> ConvertTableToTestResults(DataTable table)
        {
            var results = new List<TestResultData>();
            foreach (DataRow row in table.Rows)
            {
                var data = new TestResultData
                           {
                               TestSessionID = int.Parse(row["TestSessionID"].ToString()),
                               SuiteID = int.Parse(row["SuiteID"].ToString()),
                               SuiteName = row["SuiteName"].ToString(),
                               SuitePassed = (row["SuiteStatus"].ToString() == "P") ? true : false,
                               TestID = int.Parse(row["TestID"].ToString()),
                               StoredProcName = row["SProcName"].ToString(),
                               StoredProcPassed = (row["TestStatus"].ToString() == "P") ? true : false,
                               AssertPassed = (row["EntryType"].ToString() == "P") ? true : false,
                               LogMessage = row["LogMessage"].ToString(),
                               CreatedTime = DateTime.Parse(row["CreatedTime"].ToString())
                           };
                results.Add(data);
            }
            return results;
        }
    }
}